{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python370jvsc74a57bd088c47ce565258a22b4c52963f199570a2da8c35a1ec4323c9da01708e0a47c86",
   "display_name": "Python 3.7.0 64-bit ('Python37')"
  },
  "metadata": {
   "interpreter": {
    "hash": "88c47ce565258a22b4c52963f199570a2da8c35a1ec4323c9da01708e0a47c86"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# 导入依赖包"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy"
   ]
  },
  {
   "source": [
    "# 连接数据库"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接 Mysql 数据库:\n",
    "import pymysql\n",
    "engine = sqlalchemy.create_engine('mysql+pymysql://root:Forget,88@123.60.31.182:3306/schoolnew')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接 sqlite 数据库:\n",
    "# sqlite://<nohostname>/<path>\n",
    "# where <path> is relative:\n",
    "engine = sqlalchemy.create_engine(\"sqlite:///db.sqlite3\")"
   ]
  },
  {
   "source": [
    "# 写入数据库"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                                            password  last_login   user_id  \\\n",
       "0  pbkdf2_sha256$216000$vFoB4UcEZho5$Ne6IaU352Y5T...         NaN  18122801   \n",
       "1  pbkdf2_sha256$216000$Fxx95cQh0TbE$rK7aNnARXxud...         NaN  18122802   \n",
       "2  pbkdf2_sha256$216000$VvXmVvzK2E80$vbwauP5zUIE0...         NaN  18122803   \n",
       "3  pbkdf2_sha256$216000$Q9wRiE5mLLNg$vqndqb4rRNuB...         NaN  18122804   \n",
       "4  pbkdf2_sha256$216000$j75m0WWKFw9R$/rx2VJfHkxbz...         NaN  18122805   \n",
       "\n",
       "  user_name user_type  is_admin  is_active  \n",
       "0  student1         S         0          1  \n",
       "1  student2         S         0          1  \n",
       "2  student3         S         0          1  \n",
       "3  student4         S         0          1  \n",
       "4  student5         S         0          1  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>password</th>\n      <th>last_login</th>\n      <th>user_id</th>\n      <th>user_name</th>\n      <th>user_type</th>\n      <th>is_admin</th>\n      <th>is_active</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>pbkdf2_sha256$216000$vFoB4UcEZho5$Ne6IaU352Y5T...</td>\n      <td>NaN</td>\n      <td>18122801</td>\n      <td>student1</td>\n      <td>S</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>pbkdf2_sha256$216000$Fxx95cQh0TbE$rK7aNnARXxud...</td>\n      <td>NaN</td>\n      <td>18122802</td>\n      <td>student2</td>\n      <td>S</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>pbkdf2_sha256$216000$VvXmVvzK2E80$vbwauP5zUIE0...</td>\n      <td>NaN</td>\n      <td>18122803</td>\n      <td>student3</td>\n      <td>S</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>pbkdf2_sha256$216000$Q9wRiE5mLLNg$vqndqb4rRNuB...</td>\n      <td>NaN</td>\n      <td>18122804</td>\n      <td>student4</td>\n      <td>S</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>pbkdf2_sha256$216000$j75m0WWKFw9R$/rx2VJfHkxbz...</td>\n      <td>NaN</td>\n      <td>18122805</td>\n      <td>student5</td>\n      <td>S</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "df = pd.read_csv('./data/user.csv')\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\n",
    "    name = 'user',\n",
    "    con = engine,\n",
    "    index = False,\n",
    "    if_exists = 'append'\n",
    ")"
   ]
  },
  {
   "source": [
    "第一个参数是指定要导入到哪个表中，第二个参数是连接用的，第三个参数是要去掉索引，第四个参数是指如果数据库里已经有某个值了，就 keep both, 这个参数值有几种不同的，比如 \"覆盖\" 等."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 批量添加 按照依赖关系顺序\n",
    "tableMap = [\n",
    "    {\"csv\":\"college\",\"table\":\"CollegeTable\"},\n",
    "    {\"csv\":\"student\",\"table\":\"StudentTable\"},\n",
    "    {\"csv\":\"teacher\",\"table\":\"TeacherTable\"},\n",
    "    {\"csv\":\"course\",\"table\":\"CourseTable\"},\n",
    "    {\"csv\":\"open\",\"table\":\"OpenTable\"},\n",
    "    {\"csv\":\"score\",\"table\":\"ScoreTable\"}\n",
    "    ]\n",
    "for tableIndex in tableMap:\n",
    "   df = pd.read_csv('./data/{0}.csv'.format(tableIndex[\"csv\"]))\n",
    "   df.to_sql(\n",
    "        name = tableIndex[\"csv\"]+'table',\n",
    "        con = engine,\n",
    "        index = False,\n",
    "        if_exists = 'append'\n",
    "    ) "
   ]
  },
  {
   "source": [
    "# 读取数据表"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   id semester course_time course_id teacher_id\n",
       "0   1   202002      星期一1-2      C016       1002\n",
       "1   2   202001      星期一2-3      C003       1002\n",
       "2   3   202001      星期一1-2      C002       1003\n",
       "3   4   202001      星期三5-6      C002       1004\n",
       "4   5   202001      星期三5-7      C006       1005"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>semester</th>\n      <th>course_time</th>\n      <th>course_id</th>\n      <th>teacher_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>202002</td>\n      <td>星期一1-2</td>\n      <td>C016</td>\n      <td>1002</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>202001</td>\n      <td>星期一2-3</td>\n      <td>C003</td>\n      <td>1002</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>202001</td>\n      <td>星期一1-2</td>\n      <td>C002</td>\n      <td>1003</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>202001</td>\n      <td>星期三5-6</td>\n      <td>C002</td>\n      <td>1004</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>202001</td>\n      <td>星期三5-7</td>\n      <td>C006</td>\n      <td>1005</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "df = pd.read_sql_table('opentable', engine)\n",
    "df.head(5)"
   ]
  },
  {
   "source": [
    "# 执行Sql语句"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   user_id  college_id\n",
       "0        5           7"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>user_id</th>\n      <th>college_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5</td>\n      <td>7</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 52
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT user_id, college_id from StudentTable where English_class='C'\n",
    "'''\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df"
   ]
  },
  {
   "source": [
    "最后一个函数 pd.read_sql (), 它是 read_sql_table () 和 read_sql_query () 的集合，也就说它会根据传入的第一个参数自动判断是 table 还是 query."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "    user_id college_id\n",
       "0  18122805       CL07"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>user_id</th>\n      <th>college_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>18122805</td>\n      <td>CL07</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT user_id, college_id from StudentTable where English_class='C'\n",
    "'''\n",
    "df = pd.read_sql(query, engine)\n",
    "df"
   ]
  },
  {
   "source": [
    "# 成绩分析\n",
    "\n",
    "[参考1](https://me.bdp.cn/share/index.html?shareId=sdo_a577450540c475cf4e7ccc9f1a6760d0)\n",
    "[参考2](https://www.zhihu.com/question/24398775)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 成绩参数\n",
    "- 平均分、最高分、最低分。\n",
    "- 难度系数：难度系数是反映试题的难易程度的指标，难度系数越大自然考分就越低。难度系数这样来算：L=1—X/W其中，L为难度系数，X为样本平均得分，W为试卷总分。出（考）题（倒）无数的老师谈一点经验，试题的难度系数在0.3-0.7之间比较合适。"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   course_id  student_num  pass_num  score_max  score_min  score_avg\n",
       "0          2           12         8         91         56  78.333333\n",
       "1          3           14        11         97         56  82.214286\n",
       "2          6           10         6         91         56  71.900000\n",
       "3          7            6         6         81         61  68.500000\n",
       "4          8           15        10         91         56  74.466667"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>course_id</th>\n      <th>student_num</th>\n      <th>pass_num</th>\n      <th>score_max</th>\n      <th>score_min</th>\n      <th>score_avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>12</td>\n      <td>8</td>\n      <td>91</td>\n      <td>56</td>\n      <td>78.333333</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>3</td>\n      <td>14</td>\n      <td>11</td>\n      <td>97</td>\n      <td>56</td>\n      <td>82.214286</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6</td>\n      <td>10</td>\n      <td>6</td>\n      <td>91</td>\n      <td>56</td>\n      <td>71.900000</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>7</td>\n      <td>6</td>\n      <td>6</td>\n      <td>81</td>\n      <td>61</td>\n      <td>68.500000</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>8</td>\n      <td>15</td>\n      <td>10</td>\n      <td>91</td>\n      <td>56</td>\n      <td>74.466667</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "# 计算每学期每个开课课程的难度系数、及格人数、及格率、平均分、最高分、最低分\n",
    "query = '''\n",
    "SELECT course_id,COUNT(student_id) as student_num,SUM(case when score>=60 then 1 else 0 end) as pass_num,MAX(score) as score_max,MIN(score) as score_min,AVG(score) as score_avg\n",
    "from OpenTable as O,ScoreTable as S\n",
    "where S.open_id=O.id and semester=202001\n",
    "group by course_id\n",
    "'''\n",
    "pass_num_table = pd.read_sql_query(query, engine)\n",
    "pass_num_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   course_id  student_num  pass_num  score_max  score_min  score_avg  \\\n",
       "0          2           12         8         91         56      78.33   \n",
       "1          3           14        11         97         56      82.21   \n",
       "2          6           10         6         91         56      71.90   \n",
       "3          7            6         6         81         61      68.50   \n",
       "4          8           15        10         91         56      74.47   \n",
       "\n",
       "   pass_rate  difficulty  \n",
       "0       0.67        0.22  \n",
       "1       0.79        0.18  \n",
       "2       0.60        0.28  \n",
       "3       1.00        0.31  \n",
       "4       0.67        0.26  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>course_id</th>\n      <th>student_num</th>\n      <th>pass_num</th>\n      <th>score_max</th>\n      <th>score_min</th>\n      <th>score_avg</th>\n      <th>pass_rate</th>\n      <th>difficulty</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>12</td>\n      <td>8</td>\n      <td>91</td>\n      <td>56</td>\n      <td>78.33</td>\n      <td>0.67</td>\n      <td>0.22</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>3</td>\n      <td>14</td>\n      <td>11</td>\n      <td>97</td>\n      <td>56</td>\n      <td>82.21</td>\n      <td>0.79</td>\n      <td>0.18</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>6</td>\n      <td>10</td>\n      <td>6</td>\n      <td>91</td>\n      <td>56</td>\n      <td>71.90</td>\n      <td>0.60</td>\n      <td>0.28</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>7</td>\n      <td>6</td>\n      <td>6</td>\n      <td>81</td>\n      <td>61</td>\n      <td>68.50</td>\n      <td>1.00</td>\n      <td>0.31</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>8</td>\n      <td>15</td>\n      <td>10</td>\n      <td>91</td>\n      <td>56</td>\n      <td>74.47</td>\n      <td>0.67</td>\n      <td>0.26</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "pass_num_table[\"pass_rate\"]=pass_num_table[[\"pass_num\",\"student_num\"]].apply(lambda x:x[\"pass_num\"]/x[\"student_num\"],axis=1)\n",
    "pass_num_table[\"difficulty\"]=pass_num_table[[\"score_avg\"]].apply(lambda x:1-x[\"score_avg\"]/100,axis=1)\n",
    "pass_num_table = pass_num_table.round(2)\n",
    "pass_num_table"
   ]
  },
  {
   "source": [
    "## 成绩分布情况"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  course_id  student_num  class_A  class_B  class_C  class_D  class_E\n",
       "0      C002           12        4        4        0        0        4\n",
       "1      C003           14        4        6        0        0        4\n",
       "2      C006           10        2        2        1        1        4\n",
       "3      C007            6        0        2        0        4        0\n",
       "4      C008           15        3        5        1        1        5"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>course_id</th>\n      <th>student_num</th>\n      <th>class_A</th>\n      <th>class_B</th>\n      <th>class_C</th>\n      <th>class_D</th>\n      <th>class_E</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>C002</td>\n      <td>12</td>\n      <td>4</td>\n      <td>4</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C003</td>\n      <td>14</td>\n      <td>4</td>\n      <td>6</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C006</td>\n      <td>10</td>\n      <td>2</td>\n      <td>2</td>\n      <td>1</td>\n      <td>1</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C007</td>\n      <td>6</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>4</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C008</td>\n      <td>15</td>\n      <td>3</td>\n      <td>5</td>\n      <td>1</td>\n      <td>1</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "# 计算每学期每个开课课程的成绩分布情况\n",
    "query = '''\n",
    "SELECT course_id,COUNT(student_id) as student_num,\n",
    "SUM(case when score>=90 and score<=100 then 1 else 0 end) as class_A,\n",
    "SUM(case when score>=80 and score<90 then 1 else 0 end) as class_B,\n",
    "SUM(case when score>=70 and score<80 then 1 else 0 end) as class_C,\n",
    "SUM(case when score>=60 and score<70 then 1 else 0 end) as class_D,\n",
    "SUM(case when score>=0 and score<60 then 1 else 0 end) as class_E\n",
    "from OpenTable as O,ScoreTable as S\n",
    "where S.open_id=O.id and semester=202001\n",
    "group by course_id\n",
    "'''\n",
    "score_distribution = pd.read_sql_query(query, engine)\n",
    "score_distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'index': [0, 1, 2, 3, 4],\n",
       " 'columns': ['course_id',\n",
       "  'student_num',\n",
       "  'class_A',\n",
       "  'class_B',\n",
       "  'class_C',\n",
       "  'class_D',\n",
       "  'class_E'],\n",
       " 'data': [['C002', 12, 4, 4, 0, 0, 4],\n",
       "  ['C003', 14, 4, 6, 0, 0, 4],\n",
       "  ['C006', 10, 2, 2, 1, 1, 4],\n",
       "  ['C007', 6, 0, 2, 0, 4, 0],\n",
       "  ['C008', 15, 3, 5, 1, 1, 5]]}"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "da = score_distribution.to_dict(orient='split')\n",
    "da"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'{\"columns\":[\"course_id\",\"student_num\",\"class_A\",\"class_B\",\"class_C\",\"class_D\",\"class_E\"],\"index\":[0,1,2,3,4],\"data\":[[\"C002\",12,4,4,0,0,4],[\"C003\",14,4,6,0,0,4],[\"C006\",10,2,2,1,1,4],[\"C007\",6,0,2,0,4,0],[\"C008\",15,3,5,1,1,5]]}'"
      ]
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "das = score_distribution.to_json(orient='split')\n",
    "das"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   course_id course_name  credit  student_num  pass_num  score_max  score_min  \\\n",
       "0          2        操作系统       5           12         8         91         56   \n",
       "\n",
       "   score_avg  \n",
       "0  78.333333  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>course_id</th>\n      <th>course_name</th>\n      <th>credit</th>\n      <th>student_num</th>\n      <th>pass_num</th>\n      <th>score_max</th>\n      <th>score_min</th>\n      <th>score_avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>操作系统</td>\n      <td>5</td>\n      <td>12</td>\n      <td>8</td>\n      <td>91</td>\n      <td>56</td>\n      <td>78.333333</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "semester='202001'\n",
    "course_id='2'\n",
    "query = '''\n",
    "        SELECT C.*,\n",
    "        COUNT(student_id) as student_num,\n",
    "        SUM(case when score>=60 then 1 else 0 end) as pass_num,\n",
    "        MAX(score) as score_max,MIN(score) as score_min,\n",
    "        AVG(score) as score_avg\n",
    "        from OpenTable as O,ScoreTable as S,Coursetable as C\n",
    "        where S.open_id=O.id and C.course_id=O.course_id and semester={0} and O.course_id={1}\n",
    "        '''.format(semester,course_id)\n",
    "pass_num_table = pd.read_sql_query(query, engine)\n",
    "# pass_num_table[\"pass_rate\"]=pass_num_table[[\"pass_num\",\"student_num\"]].apply(lambda x:x[\"pass_num\"]/x[\"student_num\"],axis=1)\n",
    "# pass_num_table[\"difficulty\"]=pass_num_table[[\"score_avg\"]].apply(lambda x:1-x[\"score_avg\"]/100,axis=1)\n",
    "pass_num_table"
   ]
  },
  {
   "source": [
    "# 学生成绩分析"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  semester  avg_score\n",
       "0   202002       71.5\n",
       "1   202001       85.0\n",
       "2   202004       96.0\n",
       "3   202003       92.5"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>semester</th>\n      <th>avg_score</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>202002</td>\n      <td>71.5</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>202001</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>202004</td>\n      <td>96.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>202003</td>\n      <td>92.5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "# 学生平均成绩变化\n",
    "student_id = '18122801'\n",
    "query = '''\n",
    "        SELECT O.semester,AVG(score) as avg_score\n",
    "        FROM ScoreTable as S,OpenTable as O\n",
    "        where student_id = {0} and O.id = S.open_id\n",
    "        group by O.semester\n",
    "        '''.format(student_id)\n",
    "Avg_table = pd.read_sql_query(query, engine)\n",
    "Avg_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}